package com.feng.service;

import com.feng.dao.BillDao;
import com.feng.dao.WrongBillDao;
import com.feng.model.*;
import com.feng.util.ExcelReader;
import com.feng.util.PageResult;
import com.feng.util.Search;
import com.feng.web.controller.BillAction;
import org.apache.log4j.LogManager;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpSession;
import java.io.File;
import java.io.FileInputStream;
import java.util.*;

/**
 * 代理商Service
 */
@Service
public class BillService extends BaseService<Bill>{

	protected static Logger log = LogManager.getLogger(BillService.class);

	@Autowired
	protected BillDao billDao;

	@Autowired
	protected WrongBillDao wrongBillDao;

	public static CellStyle cellStyleRed ;
	public static CellStyle cellStyleGreen ;
	private void initStyle(Workbook wb){
		cellStyleRed = wb.createCellStyle();
		Font fontRed = wb.createFont();
		fontRed.setColor(Font.COLOR_RED);
		cellStyleRed.setFont(fontRed);

		cellStyleGreen = wb.createCellStyle();
		Font fontGreen = wb.createFont();
		fontGreen.setColor(HSSFColor.GREEN.index);
		cellStyleGreen.setFont(fontGreen);
	}



	/**
	 * 
	 * @param search
	 * @return
	 * */
	@Transactional(readOnly=true)
	public PageResult<Bill> findByPage(Search search,AdminUser user) throws Exception{
		return billDao.findByPage(search,user);
	}

	@Transactional
	public void delByAccount(Long op){
			baseDao.excuteSqlUpdate("delete from bill where op = ?",op);
	}

	@Transactional
	public void findExactRec(Bill bill,Count statics){
		if(bill.getAmount() == null){
			bill.setErrorType(4);
			bill.setReason("保单号:"+bill.getInsuranceNo()+"对应金额为空");
			statics.wrongNum ++;
		}else{
			Bill rec = billDao.findByNo(bill.getInsuranceNo());
			if(rec == null){
				billDao.save(bill);
				statics.saveNum++;
				return;
			}else if(bill.getName()!=null && !bill.getName().equalsIgnoreCase(rec.getName())){
				bill.setErrorType(1);
				statics.nomatchNum++;
				bill.setReason("被保险人不一致");
			}else if(bill.getLicensePlate()!=null && !bill.getLicensePlate().equalsIgnoreCase(rec.getLicensePlate())){
				bill.setErrorType(2);
				statics.nomatchNum++;
				bill.setReason("车牌不一致");
			}else if(Math.abs(rec.getAmount()-bill.getAmount())>0.01001 ){
				bill.setErrorType(3);
				statics.nomatchNum++;
				bill.setReason("金额不一致");
			}else{
				statics.alreadyExistNum++;
				return;
			}
		}
		WrongBill wrongBill = new WrongBill();
		BeanUtils.copyProperties(bill, wrongBill);
		wrongBillDao.save(wrongBill);
	}

	@Transactional(readOnly=true)
	public Workbook campare(Workbook workbook) throws Exception{
		Sheet sheet = workbook.getSheetAt(0);
		Row topRow = sheet.getRow(0);
		Integer i_no = null,i_name = null,i_plate = null,i_amount = null;
		Iterator<Cell> iterator = topRow.cellIterator();
		while(iterator.hasNext()){
			Cell cell = iterator.next();
			String title = cell.getStringCellValue();
			if(title.indexOf("单")>-1 && i_no== null){;
				i_no = cell.getColumnIndex();
			}else if(title.indexOf("人")>-1  && i_name== null){
				i_name = cell.getColumnIndex();
			}else if(title.indexOf("牌")>-1  && i_plate== null){
				i_plate = cell.getColumnIndex();
			}else if(title.indexOf("额")>-1  && i_amount== null){
				i_amount = cell.getColumnIndex();
			}
		}
		if(i_no == null||i_name==null||i_plate==null||i_amount==null){
			throw new Exception("子帐单格式错误,请确认!");
		}
		Cell cell_exits =  topRow.createCell(4);
		Cell cell_equal =  topRow.createCell(5);
		Cell cell_amount =  topRow.createCell(6);
		Cell cell_no = topRow.getCell(0);
		CellStyle style = cell_no.getCellStyle();
		cell_exits.setCellValue("保单是否存在");
		cell_equal.setCellValue("金额是否一致");
		cell_amount.setCellValue("总帐单金额");
		cell_exits.setCellStyle(style);
		cell_equal.setCellStyle(style);
		cell_amount.setCellStyle(style);

		CellStyle  cellStyleRed = workbook.createCellStyle();
		Font fontRed = workbook.createFont();
		fontRed.setColor(Font.COLOR_RED);
		cellStyleRed.setFont(fontRed);

		CellStyle  cellStyleGreen = workbook.createCellStyle();
		Font fontGreen = workbook.createFont();
		fontGreen.setColor(HSSFColor.GREEN.index);
		cellStyleGreen.setFont(fontGreen);


		// 获取到Excel文件中的所有行数
		// 遍历行 从第二行开始遍历
		for (int i = 1,rows = sheet.getLastRowNum(); i <= rows; i++) {
			// 读取左上端单元格
			Row data = sheet.getRow(i);
			// 行不为空
			if (data != null) {
				//获得本行中单元格的个数
				String insuranceNo =  ExcelReader.getString(data.getCell(i_no));
				String name =  ExcelReader.getString(data.getCell(i_name));
				String licensePlate =  ExcelReader.getString(data.getCell(i_plate));
				if(StringUtils.isEmpty(insuranceNo) && StringUtils.isEmpty(name) && StringUtils.isEmpty(licensePlate) ){
					break;
				}
				Bill bill = null;
				if(!StringUtils.isEmpty(insuranceNo)){
					if(insuranceNo.indexOf("'")>-1){
						System.err.println(insuranceNo);
					}
					insuranceNo = insuranceNo.replaceAll("\\W","");
					bill = billDao.findByNo(insuranceNo);
				}
				if(!StringUtils.isEmpty(name) && bill == null){
					bill = billDao.findByName(name);
				}
				if(!StringUtils.isEmpty(licensePlate) && bill == null){
					bill = billDao.findByPlate(licensePlate);
				}
				Cell cell4 = data.createCell(4);
				if(bill == null){
					cell4.setCellStyle(cellStyleRed);
					cell4.setCellValue("不存在");
				}else{
					cell4.setCellStyle(cellStyleGreen);
					cell4.setCellValue("存在");

					Double amount =  ExcelReader.getDouble(data.getCell(i_amount));
					Cell cell5 = data.createCell(5);
					if(amount!=null&& bill.getAmount()!=null && Math.abs(bill.getAmount()-amount)<=0.03001){
						cell5.setCellStyle(cellStyleGreen);
						cell5.setCellValue("一致");
					}else{
						cell5.setCellStyle(cellStyleRed);
						cell5.setCellValue("不一致");
						data.createCell(6).setCellValue(bill.getAmount());;
					}
				}
			}
		}
		return workbook;
	}

	@Transactional(readOnly=true)
	public Bill getByInsuranceNo(String insuranceNo) {
		return billDao.findByNo(insuranceNo);
	}


	public Workbook campareBill(File totalBill, File subBill, HttpSession session)  throws Exception {
		session.removeAttribute("unFinished");
		Map<String,Bill> map = transSubBillToMap(subBill);
		FileInputStream fio = new FileInputStream(totalBill);
			Workbook workbook = WorkbookFactory.create(fio);

		CellStyle  cellStyleRed = workbook.createCellStyle();
		Font fontRed = workbook.createFont();
		fontRed.setColor(Font.COLOR_RED);
		cellStyleRed.setFont(fontRed);

		CellStyle  cellStyleGreen = workbook.createCellStyle();
		Font fontGreen = workbook.createFont();
		fontGreen.setColor(HSSFColor.GREEN.index);
		cellStyleGreen.setFont(fontGreen);

			for(int sheetIndex =0;sheetIndex<workbook.getNumberOfSheets();sheetIndex++) {
				Sheet sheet = workbook.getSheetAt(sheetIndex);
				Row topRow = sheet.getRow(0);
				if (topRow == null) continue;
				Iterator<Cell> iterator = topRow.cellIterator();
				Integer i_bussi_no = null, i_compu_no = null, i_bussi_amount = null, i_compu_amount = null, i_name = null, i_license_plate = null;
				Integer i_bussi_compare = null,i_bussi_detail = null,i_compu_compare = null,i_compu_detail = null,i_compare_date = null;
				while (iterator.hasNext()) {
					Cell cell = iterator.next();
					String title = cell.getStringCellValue();
					if (title.equals(BillAction.BUSINESS_NO)) {
						i_bussi_no = cell.getColumnIndex();
					} else if (title.equals(BillAction.COMPULSORY_NO)) {
						i_compu_no = cell.getColumnIndex();
					} else if (title.equals(BillAction.BUSINESS_AMOUNT) || title.equals(BillAction.BUSINESS_AMOUNT2)) {
						i_bussi_amount = cell.getColumnIndex();
					} else if (title.equals(BillAction.COMPULSORY_AMOUNT) || title.equals(BillAction.COMPULSORY_AMOUNT2)) {
						i_compu_amount = cell.getColumnIndex();
					} else if (title.equals(BillAction.NAME)) {
						i_name = cell.getColumnIndex();
					} else if (title.equals(BillAction.LICENSE_PLATE)) {
						i_license_plate = cell.getColumnIndex();
					} else if (title.equals("商业保单金额比较")) {
						i_bussi_compare = cell.getColumnIndex();
					}
				}
				if (i_bussi_no == null || i_compu_no == null || i_bussi_amount == null || i_compu_amount == null || i_name == null || i_license_plate == null) {
					throw new Exception("总帐单格式与模板不一致,请确认!");
				}
				if(i_bussi_compare == null){
					i_bussi_compare = (int)topRow.getLastCellNum();
				}
					i_bussi_detail = i_bussi_compare + 1;
					i_compu_compare = i_bussi_compare + 2;
					i_compu_detail = i_bussi_compare + 3;
					i_compare_date = i_bussi_compare + 4;
					Cell cell_bussi = topRow.createCell(i_bussi_compare );
					Cell cell_bussi_detail = topRow.createCell(i_bussi_detail );
					Cell cell_compu = topRow.createCell(i_compu_compare);
					Cell cell_compu_detail = topRow.createCell(i_compu_detail);
					Cell cell_compare_date = topRow.createCell(i_compare_date);
					cell_bussi.setCellValue("商业保单金额比较");
					cell_bussi_detail.setCellValue("商业比较明细");
					cell_compu.setCellValue("交强保单金额比较");
					cell_compu_detail.setCellValue("交强比较明细");
					cell_compare_date.setCellValue("比较时间");
					Cell firstCell = topRow.getCell(0);
					CellStyle style = firstCell.getCellStyle();
					cell_bussi.setCellStyle(style);
					cell_bussi_detail.setCellStyle(style);
					cell_compu.setCellStyle(style);
					cell_compu_detail.setCellStyle(style);
					cell_compare_date.setCellStyle(style);

				for (int rowIndex = 4, totalRow = sheet.getLastRowNum(); rowIndex <= totalRow; rowIndex++) {
					Row row = sheet.getRow(rowIndex);
					if(rowIndex == 638){
						System.out.println(row);
					}
					if (row == null) continue;
					String name = ExcelReader.getString(row.getCell(i_name));
					String license_plate = ExcelReader.getString(row.getCell(i_license_plate));
					String bussi_no = ExcelReader.getString(row.getCell(i_bussi_no));
					String compu_no = ExcelReader.getString(row.getCell(i_compu_no));
					Double bussi_amount = ExcelReader.getDouble(row.getCell(i_bussi_amount));
					Double compu_amount = ExcelReader.getDouble(row.getCell(i_compu_amount));

					Bill bill = null;
					String key = null;
					if (!StringUtils.isEmpty(bussi_no)) {
							if (bussi_no.indexOf("'") > -1) {
								System.err.println(bussi_no);
							}
							bussi_no = bussi_no.replaceAll("\\W", "");
						    if(map.containsKey(bussi_no)){
								key = bussi_no;
							}else if(name != null && map.containsKey(name) ){
								key = name;
							}else if(license_plate != null && map.containsKey(license_plate)){
								key = license_plate;
							}
							bill = map.get(key);
							if (bill != null) {
								Cell cell = row.createCell(i_bussi_compare);
								if (bussi_amount != null && bill.getAmount() != null && Math.abs(bussi_amount-bill.getAmount()) <= 0.03001) {
									cell.setCellStyle(cellStyleGreen);
									cell.setCellValue("一致");
								}else {
									cell.setCellStyle(cellStyleRed);
									cell.setCellValue("不一致");
									Cell reason = row.createCell(i_bussi_detail);
									if(bussi_amount == null){
										reason.setCellValue("台帐商险金额为空！");
									}else if(bill.getAmount()==null){
										reason.setCellValue("手续费清单金额为空！");
									}else{
										reason.setCellValue(toFixed4(bussi_amount) + "-" + toFixed4(bill.getAmount()) + "=" + toFixed4((bussi_amount - bill.getAmount())));
									}
								}

								Cell cell_date = row.createCell(i_compare_date);
								ExcelReader.setDate(cell_date, new Date());
								map.remove(key);
							}
					}
					if (!StringUtils.isEmpty(compu_no)) {
						if (compu_no.indexOf("'") > -1) {
							System.err.println(compu_no);
						}
						compu_no = compu_no.replaceAll("\\W", "");
						if(map.containsKey(compu_no)){
							key = compu_no;
						}else if(name != null && map.containsKey(name) ){
							key = name;
						}else if(license_plate != null && map.containsKey(license_plate)){
							key = license_plate;
						}
						bill = map.get(key);
							if (bill != null) {
								Cell cell = row.createCell(i_compu_compare);
								if (compu_amount != null && bill.getAmount() != null && Math.abs(compu_amount-bill.getAmount()) <= 0.03001) {
									cell.setCellStyle(cellStyleGreen);
									cell.setCellValue("一致");
								} else {
									cell.setCellStyle(cellStyleRed);
									cell.setCellValue("不一致");
									Cell reason = row.createCell(i_compu_detail);
									if(compu_amount == null){
										reason.setCellValue("台帐交强金额为空！");
									}else if(bill.getAmount()==null){
										reason.setCellValue("手续费清单金额为空！");
									}else{
										reason.setCellValue(toFixed4(compu_amount)+"-"+toFixed4(bill.getAmount()) + "=" + toFixed4(compu_amount - bill.getAmount()));
									}

								}
								Cell cell_date = row.createCell(i_compare_date);
								ExcelReader.setDate(cell_date, new Date());
								map.remove(key);
							 }
						}
					}
				}
			session.setAttribute("unFinished",map.values());
		fio.close();
		return workbook;
	}

	private Double toFixed4(Double value){
		return ((long)(value*10000))/10000.0;
	}

	private Map<String,Bill> transSubBillToMap(File subBill) throws Exception{
		Map<String,Bill> map = new HashMap<>();
		FileInputStream fio = new FileInputStream(subBill);
		Workbook workbook = WorkbookFactory.create(fio);
		Sheet sheet = workbook.getSheetAt(0);
		Row topRow = sheet.getRow(0);
		Integer i_no = null,i_name = null,i_plate = null,i_amount = null;
		Iterator<Cell> iterator = topRow.cellIterator();
		while(iterator.hasNext()){
			Cell cell = iterator.next();
			String title = cell.getStringCellValue();
			if(title.indexOf("单")>-1 && i_no== null){;
				i_no = cell.getColumnIndex();
			}else if(title.indexOf("人")>-1  && i_name== null){
				i_name = cell.getColumnIndex();
			}else if(title.indexOf("牌")>-1  && i_plate== null){
				i_plate = cell.getColumnIndex();
			}else if(title.indexOf("额")>-1  && i_amount== null){
				i_amount = cell.getColumnIndex();
			}
		}
		if(i_no == null||i_name==null||i_plate==null||i_amount==null){
			throw new Exception("子帐单格式错误,请确认!");
		}
		// 获取到Excel文件中的所有行数
		// 遍历行 从第二行开始遍历
		for (int i = 1,rows = sheet.getLastRowNum(); i <= rows; i++) {
			// 读取左上端单元格
			Row data = sheet.getRow(i);
			// 行不为空
			if (data != null) {
				//获得本行中单元格的个数
				String insuranceNo =  ExcelReader.getString(data.getCell(i_no));
				if(insuranceNo!=null && insuranceNo.indexOf("'")>-1){
					System.err.println(insuranceNo);
					insuranceNo = insuranceNo.replaceAll("\\W", "");
				}
				String name =  ExcelReader.getString(data.getCell(i_name));
				String licensePlate =  ExcelReader.getString(data.getCell(i_plate));
				Double amount =  ExcelReader.getDouble(data.getCell(i_amount));
				if(StringUtils.isEmpty(insuranceNo) && StringUtils.isEmpty(name) && StringUtils.isEmpty(licensePlate) ){
					continue;
				}
				Bill bill = new Bill();
				bill.setInsuranceNo(insuranceNo);
				bill.setName(name);
				bill.setLicensePlate(licensePlate);
				bill.setAmount(amount);
				bill.setOptime(new Date());
				if(!StringUtils.isEmpty(insuranceNo)){
					map.put(insuranceNo,bill);
				}else if(!StringUtils.isEmpty(name)){
					map.put(name,bill);
				}else{
					map.put(licensePlate,bill);
				}
			}
		}
		fio.close();
		return map;
	}
}
